package com.admin.dao.bordao;

import com.borrower.dto.BorrowInfoBean;
import com.pojo.BorInfoBean;
import com.pojo.BorrowerBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorDaoImpl implements BorDao {
    @Override
    public boolean updateBorCancel(String sql, Connection conn, BorrowerBean bor) throws SQLException {
        boolean bool = false;
        PreparedStatement pstm = null;
        int ans;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, bor.getIsCancel());
            pstm.setLong(2, bor.getUserId());
            ans = pstm.executeUpdate();
            if (ans > 0) {
                bool = true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
        }
        return bool;
    }

    /**
     *
     * @param sql
     * @param conn
     * @return 返回查询到的用户信息集合
     * @throws SQLException
     */
    @Override
    public List<BorrowerBean> getBorInfo(String sql, Connection conn) throws SQLException {
        List<BorrowerBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BorrowerBean borObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                borObj = new BorrowerBean();
                borObj.setUserId(rs.getLong("userId"));
                borObj.setUserName(rs.getString("userName"));
                borObj.setEmail(rs.getString("email"));
                borObj.setIdCard(rs.getString("idCard"));
                borObj.setPhone(rs.getString("phone"));
                borObj.setBalance(rs.getDouble("balance"));
                borObj.setCreateDate(rs.getString("createDate"));
                borObj.setIsCancel(rs.getString("iscancel"));
//                System.out.println("bor中日期:" + borObj.getCreateDate());
                list.add(borObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }

    /**
     *
     * @param sql
     * @param conn
     * @param bor
     * @return 返回增加用户余额是否成功
     * @throws SQLException
     */
    @Override
    public boolean updateBorBalance(String sql, Connection conn, BorrowerBean bor) throws SQLException {
        boolean bool = false;
        PreparedStatement pstm = null;
        int ans;
        try {
            pstm = conn.prepareStatement(sql);


            pstm.setDouble(1, bor.getBalance());
            pstm.setString(2, bor.getIdCard());
            ans = pstm.executeUpdate();
            if (ans > 0) {
                bool = true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
        }
        return bool;
    }

    /**
     *
     * @param sql
     * @param conn
     * @param bor
     * @return 返回增加用户余额是否成功
     * @throws SQLException
     */


    /**
     * 通过传入的Sql语句查找用户信息，所有通过单字段查找用户信息都可用此方法，仅限不能重复字段
     * @param sql
     * @param conn
     * @return
     * @throws SQLException
     */
    @Override
    public BorrowerBean getBorInfoByOther(String sql, Connection conn) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BorrowerBean borObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            if (rs.next()) {
                borObj = new BorrowerBean();
                borObj.setUserId(rs.getLong("userId"));
                borObj.setUserName(rs.getString("userName"));
                borObj.setEmail(rs.getString("email"));
                borObj.setIdCard(rs.getString("idCard"));
                borObj.setPhone(rs.getString("phone"));
                borObj.setBalance(rs.getDouble("balance"));
                borObj.setCreateDate(rs.getString("createDate"));
            }

        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return borObj;
    }

    @Override
    public Double getBooksPriceSum(String sql, Connection conn)throws SQLException{

        PreparedStatement pstm = null;
        ResultSet rs = null;
       Double borObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            if (rs.next()) {

                borObj= rs.getDouble("booksum");

            }

        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return borObj;
    }

    @Override
    public Double getBorInfoSum(String sql, Connection conn)throws SQLException{

        PreparedStatement pstm = null;
        ResultSet rs = null;
        Double borObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            if (rs.next()) {

                borObj= rs.getDouble("sum");

            }

        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return borObj;
    }




    @Override
    public List<BorrowerBean> getAllBorInfo(String sql, Connection conn) throws SQLException {
        List<BorrowerBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BorrowerBean borObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                borObj = new BorrowerBean();
                borObj.setUserName(rs.getString("userName"));
                borObj.setEmail(rs.getString("email"));
                borObj.setIdCard(rs.getString("idCard"));
                borObj.setPhone(rs.getString("phone"));
                borObj.setBalance(rs.getDouble("balance"));
                borObj.setCreateDate(rs.getString("createDate"));
                list.add(borObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }


    /**
     * @param sql
     * @param conn
     * @return 返回查询到的用户信息集合
     * @throws SQLException
     */
    @Override
    public List<BorrowerBean> getBorInfoByName(String sql, Connection conn, BorrowerBean bor) throws SQLException {
        List<BorrowerBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BorrowerBean borObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, bor.getUserName());
            rs = pstm.executeQuery();
            while (rs.next()) {
                borObj = new BorrowerBean();
                borObj.setUserName(rs.getString("userName"));
                borObj.setEmail(rs.getString("email"));
                borObj.setIdCard(rs.getString("idCard"));
                borObj.setPhone(rs.getString("phone"));
                borObj.setBalance(rs.getDouble("balance"));
                borObj.setCreateDate(rs.getString("createDate"));
                list.add(borObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }

    @Override
    public boolean getIdCardBookNameAddFreeAndTime(String sql, Connection conn, String idCard, String bookName, double renewalFree, String renewalTime) throws SQLException {
        boolean bool = false;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, renewalTime);
            pstm.setDouble(2, renewalFree);
            pstm.setString(3, idCard);
            pstm.setString(4, bookName);
            int i=pstm.executeUpdate();
            if (i>0){
                bool=true;
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return bool;
    }



    /**
     * 显示所有用户借书信息
     * @param sql
     * @param conn
     * @return
     * @throws SQLException
     */
    @Override
    public List<BorrowInfoBean> getBorrowInfo(String sql, Connection conn) throws SQLException {
        List<BorrowInfoBean> list = new ArrayList<>();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        BorrowInfoBean borrowObj = null;

        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                borrowObj = new BorrowInfoBean();
                borrowObj.setUserName(rs.getString("username"));
                borrowObj.setBookName(rs.getString("bookName"));
                borrowObj.setBorDate(rs.getString("borDate"));
                borrowObj.setRepayDate(rs.getString("repayDate"));
                borrowObj.setPrice(rs.getDouble("price"));
                borrowObj.setState(rs.getString("state"));
                list.add(borrowObj);
            }
        } finally {
            if (pstm != null) {
                pstm.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return list;
    }
}
